﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IBLL;
using MisDLL;
using System.Data;
using System.Data.Common;

namespace MisBLL
{
    public class SacfDAO : BaseDAO, ISacf
    {

        protected GoodsCusDAO goodsCusDAO = new GoodsCusDAO();
        //批发销售出库
        public void Insert(IList<MisEntity.Sacf> list)
        {
            string insertSql = "exec SP_INSERT_SACF_5 '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}',NULL,'{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}'";
            object[] args = new object[29];
            foreach (MisEntity.Sacf L_Sacf in list)
            {
                MisEntity.Goods M_Goods = dbContext.Goods.First<MisEntity.Goods>(item => item.ID == L_Sacf.ID);
                MisEntity.GoodsCus M_GoodsCus = goodsCusDAO.Find(L_Sacf.CUS_ID, L_Sacf.ID);
                DateTime dt = DateTime.Now;
                args[0] = L_Sacf.NO;
                args[1] = L_Sacf.DEP_ID;
                args[2] = L_Sacf.CUS_ID;
                args[3] = L_Sacf.CUS_DEP;
                args[4] = L_Sacf.CUS_SHOP;
                args[5] = L_Sacf.SHOP_ID;
                if (M_GoodsCus != null)
                {
                    args[6] = M_GoodsCus.TYPE_JS;
                    args[7] = M_GoodsCus.DAY_JS;
                    args[8] = dt.Date.AddDays((double)M_GoodsCus.DAY_JS);
                }
                else
                {
                    args[6] = "DX";
                    args[7] = "0";
                    args[8] = dt.Date;
                }
                args[10] = L_Sacf.OPER;
                args[12] = L_Sacf.BEI;
                args[13] = L_Sacf.ID;
                args[14] = L_Sacf.DM;
                args[15] = L_Sacf.UNIT;
                args[16] = L_Sacf.SL;
                args[17] = L_Sacf.PRICE_HS;
                args[18] = L_Sacf.JE_HS;
                args[19] = L_Sacf.SL_P;
                args[21] = L_Sacf.TAX;
                args[22] = L_Sacf.PACK;
                args[23] = L_Sacf.PRICE_FS;
                args[24] = L_Sacf.JE_FS;
                if (L_Sacf.F2 == 1) args[25] = "Y";
                args[26] = "3";
                args[27] = L_Sacf.DDNO;
                args[28] = L_Sacf.PRICE_JS;
                string sql = string.Format(insertSql, args);
                dbHelper.ExecuteNonQuery(sql);
            }
        }

        //提货确认
        public void Confirm(IList<MisEntity.Sacf> list)
        {
            string confirmSql = "exec SP_SACF_TH_N '{0}','{1}','{2}','{3}','{4}','{5}'";
            object[] args = new object[6];
            foreach (MisEntity.Sacf L_Sacf in list)
            {
                DateTime dt = DateTime.Now;
                args[0] = L_Sacf.NO;
                args[1] = L_Sacf.NONO;
                args[2] = L_Sacf.ID;
                args[3] = L_Sacf.BEI;
                args[4] = L_Sacf.OP_TH;
                args[5] = dt.Date.AddDays((double)L_Sacf.DAY_JS);
                string sql = string.Format(confirmSql, args);
                dbHelper.ExecuteNonQuery(sql);
            }
        }

        //开票
        public string Billing(MisEntity.Invoice L_Invoice,IList<MisEntity.Sacf> list)
        {
            InvoiceDAO invoiceDAO = new InvoiceDAO();
            String invoiceNo=invoiceDAO.Insert(L_Invoice);
            string confirmSql = "exec SP_SACF_FP '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}'";
            object[] args = new object[8];
            foreach (MisEntity.Sacf L_Sacf in list)
            {
                DateTime dt = DateTime.Now;
                args[0] = L_Sacf.NO;
                args[1] = L_Sacf.NONO;
                args[2] = L_Sacf.ID;
                args[3] = "Y";
                args[4] = L_Sacf.OP_KP;
                args[5] = L_Sacf.NO_CUS;
                args[6] = L_Sacf.F7;
                args[7] = invoiceNo;
                string sql = string.Format(confirmSql, args);
                dbHelper.ExecuteNonQuery(sql);
            }
            return invoiceNo;
        }

        //根据单号查询批发销售信息
        public IList<MisEntity.Sacf> FindAll(string No, String DepId)
        {
            var query = from a in dbContext.Sacf
                        where a.NO == No && a.DEP_ID == DepId 
                        select a;
            if (query.Count<MisEntity.Sacf>() == 0)
                return null;
            return query.ToList<MisEntity.Sacf>();
        }

        public IList<MisEntity.Sacf> FindAll(string No)
        {
            var query = from a in dbContext.Sacf
                        where a.NO == No && a.F2!='0'
                        select a;
            if (query.Count<MisEntity.Sacf>() == 0)
                return null;
            return query.ToList<MisEntity.Sacf>();
        }

        //获取开发票中的商品数据
        public DataTable GetAll(string No)
        {
            string sql = string.Format("select s.no,s.nono,s.id,g.goods,s.unit,s.sl,s.price_fs,s.je_fs,s.tax from sacf_tab s,goods_tab g where s.id=g.id and s.f1='1' and s.f2<>'0' and s.f3='' and s.no='{0}'", No);
            return dbHelper.ExecuteDataTable(sql);
        }

        //获取销售和商品数据
        public DataTable GetAll(string sql, Dictionary<string, object> dictionary)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select m.no,m.dep_id,m.shop_id,m.cus_id,m.cus_dep,m.cus_shop,m.type_js,m.day_js,m.id,g.goods,m.sl_p,m.sl,m.je_hs,m.f1,m.f2,m.f3,m.f4,m.f8,m.date,m.d_th,m.d_kp,m.d_js,m.d_ok");
            sb.Append(",m.no_cus,m.price_fs,m.je_fs,m.price_hs,m.nono,m.dm,m.unit,m.oper,m.op_th,m.op_kp,m.op_js,m.op_ok,m.date_js,m.tax ");
            sb.Append(" from sacf_tab m,goods_tab g where m.id=g.id ").Append(sql).Append(" order by m.no");
            DbCommand cmd = dbHelper.GetSqlStringCommond(sb.ToString());
            dbHelper.AddParameter(cmd, dictionary);
            return dbHelper.ExecuteDataTable(cmd);
        }

        //获取已开票的销售数据
        public DataTable GetBillingAll(string sql, Dictionary<string, object> dictionary)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select no_cus,no_js,dep_id,cus_id,cus_dep,d_kp,f3,tax,sum(je_js) as js,sum(je_hs) as hs,sum(je_fs) as fs from sacf_tab m where ");
            sb.Append(sql).Append(" and f3='1' and f4='' group by no_cus,no_js,dep_id,cus_id,cus_dep,d_kp,f3,tax order by no_cus");
            DbCommand cmd = dbHelper.GetSqlStringCommond(sb.ToString());
            dbHelper.AddParameter(cmd, dictionary);
            return dbHelper.ExecuteDataTable(cmd);
        }

        //取消发票
        public void clearBilling(IList<MisEntity.Sacf> list)
        {
            string insertSql = "exec SP_FP_Back '{0}','{1}','{2}'";
            object[] args = new object[3];
            foreach (MisEntity.Sacf L_Sacf in list)
            {
                args[0] = L_Sacf.NO_CUS;
                args[1] = L_Sacf.NO_JS;
                args[2] = L_Sacf.OPER;
                string sql = string.Format(insertSql, args);
                dbHelper.ExecuteNonQuery(sql);
            }
        }

        //获取到款确认的销售数据
        public DataTable GetArticleAll(string sql, Dictionary<string, object> dictionary)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select NO,ID,CUS_ID,CUS_DEP,CUS_SHOP,tax,SL,SL_P,PRICE_HS,PRICE_FS,JE_HS,JE_FS,F1,DATE,F2,D_TH,F3,D_KP,F4,D_JS,F5,D_OK,PRICE_JS,JE_JS,NO_JS,SHOP_ID from SACF_tab M where ");
            sb.Append(sql);
            DbCommand cmd = dbHelper.GetSqlStringCommond(sb.ToString());
            dbHelper.AddParameter(cmd, dictionary);
            return dbHelper.ExecuteDataTable(cmd);
        }

        //获取到款确认的销售数据(按发票)
        public DataTable GetArticleNoAll(string sql, Dictionary<string, object> dictionary)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select NO,max(SHOP_ID),max(CUS_ID),max(CUS_DEP),max(CUS_SHOP),sum(JE_FS),sum(JE_HS),sum(SL_P),");
            sb.Append("max(TAX),MAX(DATE) from SACF_tab M where F2='1' and F3='1' and F4=''").Append(sql).Append(" group by NO");
            DbCommand cmd = dbHelper.GetSqlStringCommond(sb.ToString());
            dbHelper.AddParameter(cmd, dictionary);
            return dbHelper.ExecuteDataTable(cmd);
        }

        //获取到款确认中的商品数据
        public DataTable GetArticleGoodsAll(string No)
        {
            string sql = string.Format("select s.no,s.nono,s.id,g.goods,s.unit,s.sl,s.price_fs,s.je_fs,s.tax,s.NO_JS from sacf_tab s,goods_tab g where s.id=g.id and s.f2='1' and s.f3='1' and s.f4='' and s.no='{0}'", No);
            return dbHelper.ExecuteDataTable(sql);
        }

        //到款确认
        public void Article(IList<MisEntity.Sacf> list)
        {
            string confirmSql = "exec SP_SACF_SK '{0}','{1}','{2}','{3}','{4}',null,'{5}','{6}'";
            object[] args = new object[8];
            foreach (MisEntity.Sacf L_Sacf in list)
            {
                DateTime dt = DateTime.Now;
                args[0] = L_Sacf.NO;
                args[1] = L_Sacf.NONO;
                args[2] = L_Sacf.ID;
                args[3] = "Y";
                args[4] = L_Sacf.OPER;
                args[5] = L_Sacf.F7;
                args[6] = L_Sacf.NO_JS;
                string sql = string.Format(confirmSql, args);
                dbHelper.ExecuteNonQuery(sql);
            }
        }

        //废弃发票
        public void discardBilling(IList<MisEntity.Sacf> list)
        {
            string insertSql = "exec SP_sacf_sk_no '{0}','{1}','{2}',null,'{3}','{4}'";
            object[] args = new object[5];
            foreach (MisEntity.Sacf L_Sacf in list)
            {
                args[0] = L_Sacf.NO_CUS;
                args[1] = L_Sacf.NO_JS;
                args[2] = L_Sacf.D_KP;
                args[3] = L_Sacf.OPER;
                args[4] = "Y";
                string sql = string.Format(insertSql, args);
                dbHelper.ExecuteNonQuery(sql);
            }
        }

        //获取批销业务进程数据
        public DataTable GetVolumeAll(string sql, Dictionary<string, object> dictionary)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select bf.no,bf.dep_id,bf.shop_id,bf.cus_id,bf.cus_dep,bf.cus_shop,bf.type_js,bf.day_js,bf.id,g.goods,bf.sl_p,bf.sl,bf.je_hs,bf.f1,bf.f2,bf.f3,bf.f4,bf.f8,bf.date,bf.d_th,bf.d_kp,bf.d_js,bf.d_ok");
            sb.Append(",bf.no_cus,bf.price_l,bf.pricei,bf.price_hs,bf.nono,bf.dm,bf.unit,bf.oper,bf.op_th,bf.op_kp,bf.op_js,bf.op_ok,bf.date_js,bf.bei ");
            sb.Append("from sacf_tab bf,goods_tab g where bf.id=g.id").Append(sql);
            DbCommand cmd = dbHelper.GetSqlStringCommond(sb.ToString());
            dbHelper.AddParameter(cmd, dictionary);
            return dbHelper.ExecuteDataTable(cmd);
        }

        //获取单据结算数据
        public DataTable GetDocumentsAll(string sql, Dictionary<string, object> dictionary)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select M.no,M.dep_id,M.shop_id,M.cus_id,M.cus_dep,M.cus_shop,M.type_js,M.day_js,M.id,g.goods,M.sl_p,M.sl,M.je_hs,M.f1,M.f2,M.f3,M.f4,M.f8,M.date,M.d_th,M.d_kp,M.d_js,M.d_ok");
            sb.Append(",M.no_cus,M.price_l,M.pricei,M.price_hs,M.nono,M.dm,M.unit,M.oper,M.op_th,M.op_kp,M.op_js,M.op_ok,M.date_js,m.bei ");
            sb.Append("from sacf_tab m,goods_tab g where M.id=g.id").Append(sql);
            DbCommand cmd = dbHelper.GetSqlStringCommond(sb.ToString());
            dbHelper.AddParameter(cmd, dictionary);
            return dbHelper.ExecuteDataTable(cmd);
        }

        //获取单据结算数据
        public DataTable GetRecordationAll(string sql, Dictionary<string, object> dictionary)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT DATE,'销售  ',NO,SL,PRICE_HS,PRICEI,JE_HS,SLKC,JEKC,shop_id,CUS_SHOP,DEP_ID,CUS_DEP,CUS_ID,f1,f2,f3,f4,bei FROM SACF_TAB WHERE ID=@id and no!='0000000000'").Append(sql);
            sb.Append(" UNION ALL SELECT DATE,'  进货',NO,SL,PRICE,JE/SL,JE,SLKC,JEKC,shop_id,SUPP_ID,DEP_ID,'',JHTYPE,'','','','',no_bei FROM BACF_TAB WHERE ID=@id").Append(sql);
            sb.Append(" UNION ALL SELECT DATE,'  退货',NO,SL,PRICE,JE/SL,JE,SLKC,JEKC,shop_id,SUPP_ID,DEP_ID,'',JHTYPE,'','','','',no_bei FROM RACF_TAB WHERE ID=@id").Append(sql);
            sb.Append(" ORDER BY 1");
            DbCommand cmd = dbHelper.GetSqlStringCommond(sb.ToString());
            dbHelper.AddParameter(cmd, dictionary);
            return dbHelper.ExecuteDataTable(cmd);
        }

        //获取销售查询数据
        public DataTable GetEnquiriesAll(string sql, Dictionary<string, object> dictionary, string linker, string linker1)
        {
            
            StringBuilder sb = new StringBuilder();
            sb.Append("select M.DATE,M.NO,M.shop_ID,M.dep_id,M.ID,g.goods,m.sl_p,m.pack,m.price_js,M.SL,M.PRICE_hs,M.JE_hs,m.pricei,M.cus_id,M.cus_shop,M.cus_dep,M.OPER,M.type_js,M.BEI,M.F1,M.F2,M.f3,");
            sb.Append("m.slkc,m.jekc,m.no_cus,m.d_th,m.d_kp,m.d_js,m.d_ok,m.type1,m.type2,m.type3,m.f4,m.dm,m.f6,m.unit,m.ddno ");
            sb.Append("from SACF_tab M,GOODS_TAB G");
            if ((null != linker && !"".Equals(linker)) || (null != linker1 && !"".Equals(linker1)))
            {
                sb.Append(",customer_tab c");
            }
            sb.Append("  where m.id=g.id");
            if (null != linker && !"".Equals(linker))
            {
                sb.Append(" and m.cus_id=c.cus_id and c.linker=@linker");
                dictionary.Add("@linker", linker);
            }
            if (null != linker1 && !"".Equals(linker1))
            {
                sb.Append(" and m.cus_id=c.cus_id and c.f2=@f2");
                dictionary.Add("@f2", linker1);
            }
            sb.Append(sql);
            DbCommand cmd = dbHelper.GetSqlStringCommond(sb.ToString());
            dbHelper.AddParameter(cmd, dictionary);
            return dbHelper.ExecuteDataTable(cmd);
        }


        //获取销售单据汇总数据
        public DataTable GetEnquiriesStatisticsAll(string sql, Dictionary<string, object> dictionary, string linker, string linker1)
        {

            StringBuilder sb = new StringBuilder();
            sb.Append("select M.DATE,M.NO,M.shop_ID,M.dep_id,sum(m.price_js) as price_js,sum(M.PRICE_hs) as PRICE_hs,sum(M.JE_hs) as JE_hs,sum(m.pricei) as pricei");
            sb.Append(" from SACF_tab M,GOODS_TAB G");
            if ((null != linker && !"".Equals(linker)) || (null != linker1 && !"".Equals(linker1)))
            {
                sb.Append(",customer_tab c");
            }
            sb.Append("  where m.id=g.id");
            if (null != linker && !"".Equals(linker))
            {
                sb.Append(" and m.cus_id=c.cus_id and c.linker=@linker");
                dictionary.Add("@linker", linker);
            }
            if (null != linker1 && !"".Equals(linker1))
            {
                sb.Append(" and m.cus_id=c.cus_id and c.f2=@f2");
                dictionary.Add("@f2", linker1);
            }
            sb.Append(sql).Append(" group by M.DATE,M.NO,M.shop_ID,M.dep_id");
            DbCommand cmd = dbHelper.GetSqlStringCommond(sb.ToString());
            dbHelper.AddParameter(cmd, dictionary);
            return dbHelper.ExecuteDataTable(cmd);
        }



        //获取销售查询数据
        public DataTable GetStatisticsAll(string sql, Dictionary<string, object> dictionary, string tjType, string linker1)
        {
            StringBuilder sb = new StringBuilder();
            if ("按单号客户分店业务员".Equals(tjType))
            {
                sb.Append("select m.no,m.date,M.cus_id,m.cus_shop,m.oper,c.allname,s.shop,sum(M.SL),sum(m.je_hs),sum(m.je_fs),sum(m.sl*m.pricei),sum(m.sl_p) ");
                sb.Append("from Sacf_tab M,customer_tab c,cusshop_tab s where m.cus_id=c.cus_id and m.cus_id=s.cus_id and m.cus_shop=s.cus_shop");
                if (null != linker1 && !"".Equals(linker1))
                {
                    sb.Append(" and c.f2=@f2");
                    dictionary.Add("@f2", linker1);
                }
                sb.Append(sql).Append(" GROUP BY M.cus_id,m.cus_shop,m.no,m.date,m.oper,c.allname,s.shop");
            }
            else if ("按发票号客户部门".Equals(tjType))
            {
                sb.Append("select m.no_cus,m.d_kp,M.cus_id,m.cus_dep,m.oper,c.allname,s.depart,sum(M.SL),sum(m.je_hs),sum(m.je_fs),sum(m.sl*m.pricei),sum(m.sl_p) ");
                sb.Append("from Sacf_tab M,customer_tab c,cusdep_tab s where m.cus_id=c.cus_id and m.cus_id=s.cus_id and m.cus_dep=s.cus_dep");
                if (null != linker1 && !"".Equals(linker1))
                {
                    sb.Append(" and c.f2=@f2");
                    dictionary.Add("@f2", linker1);
                }
                sb.Append(sql).Append(" GROUP BY M.cus_id,m.cus_dep,m.no_cus,m.d_kp,m.oper,c.allname,s.depart");
            }
            else if ("按发票号".Equals(tjType))
            {
                sb.Append("select m.no_cus,M.cus_id,sum(M.SL),sum(m.je_hs),sum(m.je_fs),sum(m.sl*m.pricei),sum(m.sl_p) from Sacf_tab M,goods_tab g ").Append(getYwyStr(linker1, dictionary)).Append(" and m.id=g.id").Append(sql);
                sb.Append(" GROUP BY m.no_cus,M.cus_id");
            }
            else if ("按部门+商品".Equals(tjType))
            {
                sb.Append("select m.dep_id,M.id,g.goods,sum(M.SL),sum(m.je_hs),sum(m.je_fs),sum(m.sl*m.pricei),sum(m.sl_p) from Sacf_tab M,goods_tab g ").Append(getYwyStr(linker1, dictionary)).Append(" and m.id=g.id").Append(sql);
                sb.Append(" GROUP BY m.dep_id,m.id,g.goods");
            }
            else if ("按部门+客户".Equals(tjType))
            {
                sb.Append("select m.dep_id,M.cus_id,sum(M.SL),sum(m.je_hs),sum(m.je_fs),sum(m.sl*m.pricei),sum(m.sl_p) from Sacf_tab M,goods_tab g ").Append(getYwyStr(linker1, dictionary)).Append(" and m.id=g.id").Append(sql);
                sb.Append(" GROUP BY m.dep_id,M.cus_id");
            }
            else if ("按供应商+客户".Equals(tjType))
            {
                sb.Append("select g.supp_id,M.cus_id,sum(M.SL),sum(m.je_hs),sum(m.je_fs),sum(m.sl*m.pricei),sum(m.sl_p) from Sacf_tab M,goods_tab g ").Append(getYwyStr(linker1, dictionary)).Append(" and m.id=g.id").Append(sql);
                sb.Append(" GROUP BY g.supp_id,M.cus_id");
            }
            else if ("按日期".Equals(tjType) || "按日期+商场".Equals(tjType) || "按日期+部门".Equals(tjType))
            {
                sb.Append("select convert(char(12),m.date,102),m.dep_id,sum(M.SL),sum(m.je_hs),sum(m.je_fs),sum(m.sl*m.pricei),sum(m.sl_p) from Sacf_tab M,goods_tab g ").Append(getYwyStr(linker1, dictionary)).Append(" and m.id=g.id").Append(sql);
                sb.Append(" GROUP BY convert(char(12),m.date,102),m.dep_id");
            }
            else if ("按仓库".Equals(tjType))
            {
                sb.Append("select m.dep_id,M.shop_id,sum(M.SL),sum(m.je_hs),sum(m.je_fs),sum(m.sl*m.pricei),sum(m.sl_p) from Sacf_tab M,goods_tab g ").Append(getYwyStr(linker1, dictionary)).Append(" and m.id=g.id").Append(sql);
                sb.Append(" GROUP BY m.dep_id,m.shop_id");
            }
            else if ("按销售员".Equals(tjType))
            {
                sb.Append("select m.dep_id,M.oper,sum(M.SL),sum(m.je_hs),sum(m.je_fs),sum(m.sl*m.pricei),sum(m.sl_p) from Sacf_tab M,goods_tab g ").Append(getYwyStr(linker1, dictionary)).Append(" and m.id=g.id").Append(sql);
                sb.Append(" GROUP BY m.dep_id,m.oper");
            }
            else if ("按部门".Equals(tjType) || "按商场".Equals(tjType))
            {
                sb.Append("select M.dep_id,'',sum(M.SL),sum(m.je_hs),sum(m.je_fs),sum(m.sl*m.pricei),sum(m.sl_p) from Sacf_tab M,goods_tab g ").Append(getYwyStr(linker1, dictionary)).Append(" and m.id=g.id").Append(sql);
                sb.Append(" GROUP BY m.dep_id");
            }
            else if ("按商品".Equals(tjType))
            {
                sb.Append("select M.id,g.goods,sum(M.SL),sum(m.je_hs),sum(m.je_fs),sum(m.sl*m.pricei),sum(m.sl_p) from Sacf_tab M,goods_tab g ").Append(getYwyStr(linker1, dictionary)).Append(" and m.id=g.id").Append(sql);
                sb.Append(" GROUP BY m.id,g.goods");
            }
            else if ("按客户分店".Equals(tjType))
            {
                sb.Append("select M.cus_id,m.cus_shop,sum(M.SL),sum(m.je_hs),sum(m.je_fs),sum(m.sl*m.pricei),sum(m.sl_p) from Sacf_tab M,goods_tab g ").Append(getYwyStr(linker1, dictionary)).Append(" and m.id=g.id").Append(sql);
                sb.Append(" GROUP BY m.cus_id,M.cus_shop");
            }
            else if ("按客户部门".Equals(tjType))
            {
                sb.Append("select M.cus_id,m.cus_dep,sum(M.SL),sum(m.je_hs),sum(m.je_fs),sum(m.sl*m.pricei),sum(m.sl_p) from Sacf_tab M,goods_tab g ").Append(getYwyStr(linker1, dictionary)).Append(" and m.id=g.id").Append(sql);
                sb.Append(" GROUP BY m.cus_id,M.cus_dep");
            }
            else if ("按客户".Equals(tjType))
            {
                sb.Append("select M.cus_id,c.customer,sum(M.SL),sum(m.je_hs),sum(m.je_fs),sum(m.sl*m.pricei),sum(m.sl_p) from Sacf_tab M,goods_tab g ").Append(getYwyStr(linker1, dictionary)).Append(" and m.cus_id=c.cus_id and m.id=g.id").Append(sql);
                sb.Append(" GROUP BY M.cus_id,c.customer");
            }
            else if ("按供应商".Equals(tjType))
            {
                sb.Append("select g.supp_id,s.supply,sum(M.SL),sum(m.je_hs),sum(m.je_fs),sum(m.sl*m.pricei),sum(m.sl_p) from Sacf_tab M,goods_tab g,supply_tab s ").Append(getYwyStr(linker1, dictionary)).Append(" and m.id=g.id and g.supp_id=s.supp_id").Append(sql);
                sb.Append(" GROUP BY g.supp_id,s.supply");
            }
            DbCommand cmd = dbHelper.GetSqlStringCommond(sb.ToString());
            dbHelper.AddParameter(cmd, dictionary);
            return dbHelper.ExecuteDataTable(cmd);
        }

        private string getYwyStr(string linker1, Dictionary<string, object> dictionary)
        {
            StringBuilder sb = new StringBuilder();
            if (null != linker1 && !"".Equals(linker1))
            {
                sb.Append(",customer_tab c");
            }
            sb.Append(" where 1=1 ");
            if (null != linker1 && !"".Equals(linker1))
            {
                sb.Append(" and m.cus_id=c.cus_id and c.f2=@f2");
                dictionary.Add("@f2", linker1);
            }
            return sb.ToString();
        }

        //获取销售排行榜统计数据
        public DataTable GetRankingAll(string sql, Dictionary<string, object> dictionary, string tjType, String order)
        {
            StringBuilder sb = new StringBuilder();
            if ("按供应商".Equals(tjType))
            {
                sb.Append("SELECT SUM(M.SL) AS SSL,SUM(M.JE_hs) AS SJE,SUM(M.JE_fs) AS SJEf,SUM(M.JE_hs-M.SL*M.PRICEI) AS SLL,g.SUPP_ID,D.SUPPLY FROM SACF_TAB M,SUPPLY_TAB D,goods_tab g WHERE m.id=g.id and g.SUPP_ID=D.SUPP_ID");
                sb.Append(sql).Append(" GROUP BY g.SUPP_ID,D.SUPPLY").Append(order);
            }
            else if ("按客户".Equals(tjType))
            {
                sb.Append("SELECT SUM(M.SL) AS SSL,SUM(M.JE_hs) AS SJE,SUM(M.JE_fs) AS SJEf,SUM(M.JE_hs-M.SL*M.PRICEI) AS SLL,M.cus_ID,c.customer FROM SACF_TAB M,customer_TAB c,goods_tab g WHERE m.id=g.id and M.cus_ID=c.cus_ID");
                sb.Append(sql).Append(" GROUP BY M.cus_ID,c.customer").Append(order);
            }
            else if ("按部门".Equals(tjType))
            {
                sb.Append("SELECT SUM(M.SL) AS SSL,SUM(M.JE_hs) AS SJE,SUM(M.JE_fs) AS SJEf,SUM(M.JE_hs-M.SL*M.PRICEI) AS SLL,M.dep_ID,c.depart FROM SACF_TAB M,depart_TAB c,goods_tab g WHERE m.id=g.id and M.dep_ID=c.dep_ID");
                sb.Append(sql).Append(" GROUP BY M.dep_ID,c.depart").Append(order);
            }
            else
            {
                sb.Append("SELECT SUM(M.SL) AS SSL,SUM(M.JE_hs) AS SJE,SUM(M.JE_fs) AS SJEf,SUM(M.JE_hs-M.SL*M.PRICEI) AS SLL,M.ID,g.goods FROM SACF_TAB M,goods_tab g WHERE m.id=g.id");
                sb.Append(sql).Append(" GROUP BY M.id,g.goods").Append(order);
            }
            DbCommand cmd = dbHelper.GetSqlStringCommond(sb.ToString());
            dbHelper.AddParameter(cmd, dictionary);
            return dbHelper.ExecuteDataTable(cmd);
        }

    }
}
